<template>
  <div class="container">
    <header>
      <h1>SQLite 数据库测试应用</h1>
      <p class="subtitle">使用HTML5+技术实现安卓SQLite数据库操作</p>
    </header>
    
    <div class="card">
      <div class="card-header">
        <i class="material-icons">build</i>
        <span>数据库操作</span>
      </div>
      <div class="card-body">
        <div class="btn-group">
          <button class="btn" @click="openDatabase">
            <i class="material-icons">create_new_folder</i>
            创建/打开数据库
          </button>
          <button class="btn" @click="createTable">
            <i class="material-icons">grid_on</i>
            创建数据表
          </button>
          <button class="btn btn-success" @click="insertData">
            <i class="material-icons">add</i>
            插入测试数据
          </button>
        </div>
        
        <div class="btn-group">
          <button class="btn" @click="queryData">
            <i class="material-icons">search</i>
            查询所有数据
          </button>
          <button class="btn" @click="updateData">
            <i class="material-icons">edit</i>
            更新最后一条
          </button>
          <button class="btn btn-delete" @click="deleteLast">
            <i class="material-icons">delete</i>
            删除最后一条
          </button>
        </div>
        
        <div class="status-bar">
          <i class="material-icons">info</i>
          <span id="db-status">数据库状态: {{ dbStatusText }}</span>
        </div>
        
        <h3>操作日志:</h3>
        <div class="result-area" id="log-area">
          <div v-for="(log, index) in logs" :key="index" class="log-entry" :class="log.type">
            {{ log.message }}
          </div>
        </div>
      </div>
    </div>
    
    <div class="card">
      <div class="card-header">
        <i class="material-icons">list</i>
        <span>数据表内容</span>
      </div>
      <div class="card-body">
        <div id="data-display">
          <p v-if="!tableData.length">数据将在此处显示...</p>
          <table v-else class="data-table">
            <thead>
              <tr>
                <th>ID</th>
                <th>姓名</th>
                <th>邮箱</th>
                <th>年龄</th>
                <th>创建时间</th>
              </tr>
            </thead>
            <tbody>
              <tr v-for="row in tableData" :key="row.id">
                <td>{{ row.id }}</td>
                <td>{{ row.name }}</td>
                <td>{{ row.email || 'N/A' }}</td>
                <td>{{ row.age || 'N/A' }}</td>
                <td>{{ row.created_at || 'N/A' }}</td>
              </tr>
            </tbody>
          </table>
        </div>
      </div>
    </div>
    
    <footer>
      <p>使用 HBuilderX 创建 | SQLite 数据库测试应用 | 可在安卓设备上运行</p>
    </footer>
  </div>
</template>

<script>
import DB from "@/api/db";

export default {
  name: 'SQLiteTestApp',
  data() {
    return {
      db: null,
      dbName: "testDB",
      tableName: "users",
      logs: [
        { message: "应用已就绪，请点击上方按钮测试数据库功能", type: "info" }
      ],
      tableData: [],
      dbStatusText: "未打开"
    };
  },
  mounted() {
    document.addEventListener("plusready", this.onPlusReady);
  },
  beforeDestroy() {
    document.removeEventListener("plusready", this.onPlusReady);
  },
  methods: {
    onPlusReady() {
      this.addLog("应用初始化完成，准备就绪");
    },
    addLog(message, type = "info") {
      this.logs.push({ message, type });
      this.$nextTick(() => {
        const logArea = document.getElementById("log-area");
        logArea.scrollTop = logArea.scrollHeight;
      });
    },
    updateDbStatus() {
      if (this.db) {
        this.dbStatusText = `已打开 (${this.dbName})`;
      } else {
        this.dbStatusText = "未打开";
      }
    },
    async openDatabase() {
      // 关闭已打开的数据库
      if (this.db) {
        // plus.sqlite.closeDatabase({ name: this.dbName });
		
		await DB.closeSqlite()
        this.db = null;
      }
	  
	  // 打开数据库
	  await DB.openSqlite();
	  this.db = this.dbName;
	  console.log('this.db: ', this.db)
	  this.addLog(`数据库打开/创建成功: ${this.dbName}`, "success");
	  this.updateDbStatus();
    },
    createTable() {
      if (!this.db) {
        this.addLog("请先打开数据库", "error");
        return;
      }
      
      const sql = `CREATE TABLE IF NOT EXISTS ${this.tableName} (
                  id INTEGER PRIMARY KEY AUTOINCREMENT, 
                  name TEXT, 
                  email TEXT, 
                  age INTEGER, 
                  created_at DATETIME DEFAULT CURRENT_TIMESTAMP)`;
      
      plus.sqlite.executeSql({
        name: this.db,
        sql: sql,
        success: () => {
          this.addLog(`数据表创建成功: ${this.tableName}`, "success");
        },
        fail: (e) => {
          this.addLog(`创建数据表失败: ${JSON.stringify(e)}`, "error");
        }
      });
    },
    async insertData() {
      if (!this.db) {
        this.addLog("请先打开数据库", "error");
        return;
      }
      
      const names = ["张三", "李四", "王五", "赵六", "钱七"];
      const emails = ["zhangsan@test.com", "lisi@test.com", "wangwu@test.com", "zhaoliu@test.com", "qianqi@test.com"];
      const ages = [25, 30, 28, 35, 32];
      
      const randomIndex = Math.floor(Math.random() * names.length);
      const name = names[randomIndex];
	  console.log('name: ', name)
	  this.addLog(`name: ${name}`, "success");
	  
      const email = emails[randomIndex];
      const age = ages[randomIndex];
	  
	  let data = `'${name}','${email}',${age}`;
	  let condition = "'name','email','age'";
	  await DB.insertTableData("users", data, condition);
	  	  
    },
    queryData() {
      if (!this.db) {
        this.addLog("请先打开数据库", "error");
        return;
      }
      
      const sql = `SELECT * FROM ${this.tableName}`;
      
      plus.sqlite.selectSql({
        name: this.db,
        sql: sql,
        success: (data) => {
          if (data && data.length > 0) {
            this.addLog(`查询成功，找到${data.length}条记录`, "success");
            this.tableData = data;
          } else {
            this.addLog("表中没有数据", "info");
            this.tableData = [];
          }
        },
        fail: (e) => {
          this.addLog(`查询数据失败: ${JSON.stringify(e)}`, "error");
        }
      });
    },
    updateData() {
      if (!this.db) {
        this.addLog("请先打开数据库", "error");
        return;
      }
      
      // 先获取最后一条记录的ID
      plus.sqlite.selectSql({
        name: this.db,
        sql: `SELECT id FROM ${this.tableName} ORDER BY id DESC LIMIT 1`,
        success: (data) => {
          if (data && data.length > 0) {
            const lastId = data[0].id;
            const newAge = Math.floor(Math.random() * 20) + 20; // 随机年龄
            
            const sql = `UPDATE ${this.tableName} SET age = ? WHERE id = ?`;
            
            plus.sqlite.executeSql({
              name: this.db,
              sql: sql,
              args: [newAge, lastId],
              success: () => {
                this.addLog(`更新成功: ID ${lastId} 的年龄改为 ${newAge}`, "success");
                this.queryData(); // 刷新显示
              },
              fail: (e) => {
                this.addLog(`更新数据失败: ${JSON.stringify(e)}`, "error");
              }
            });
          } else {
            this.addLog("没有数据可更新", "info");
          }
        },
        fail: (e) => {
          this.addLog(`获取最后一条记录失败: ${JSON.stringify(e)}`, "error");
        }
      });
    },
    deleteLast() {
      if (!this.db) {
        this.addLog("请先打开数据库", "error");
        return;
      }
      
      // 先获取最后一条记录的ID
      plus.sqlite.selectSql({
        name: this.db,
        sql: `SELECT id FROM ${this.tableName} ORDER BY id DESC LIMIT 1`,
        success: (data) => {
          if (data && data.length > 0) {
            const lastId = data[0].id;
            
            const sql = `DELETE FROM ${this.tableName} WHERE id = ?`;
            
            plus.sqlite.executeSql({
              name: this.db,
              sql: sql,
              args: [lastId],
              success: () => {
                this.addLog(`删除成功: ID ${lastId} 的记录已删除`, "success");
                this.queryData(); // 刷新显示
              },
              fail: (e) => {
                this.addLog(`删除数据失败: ${JSON.stringify(e)}`, "error");
              }
            });
          } else {
            this.addLog("没有数据可删除", "info");
          }
        },
        fail: (e) => {
          this.addLog(`获取最后一条记录失败: ${JSON.stringify(e)}`, "error");
        }
      });
    }
  }
};
</script>

<style>
* {
  margin: 0;
  padding: 0;
  box-sizing: border-box;
  font-family: 'Roboto', 'Helvetica Neue', Arial, sans-serif;
}

body {
  background: linear-gradient(135deg, #1a237e, #4a148c);
  color: #333;
  min-height: 100vh;
  padding: 20px;
}

.container {
  max-width: 800px;
  margin: 0 auto;
  padding: 20px;
}

header {
  text-align: center;
  margin-bottom: 30px;
  color: white;
  padding: 20px;
  background: rgba(0, 0, 0, 0.2);
  border-radius: 15px;
  box-shadow: 0 4px 20px rgba(0, 0, 0, 0.2);
}

h1 {
  font-size: 2.2rem;
  margin-bottom: 10px;
  text-shadow: 0 2px 4px rgba(0,0,0,0.3);
}

.subtitle {
  font-size: 1.1rem;
  opacity: 0.9;
}

.card {
  background: white;
  border-radius: 12px;
  box-shadow: 0 6px 20px rgba(0, 0, 0, 0.15);
  margin-bottom: 25px;
  overflow: hidden;
  transition: transform 0.3s, box-shadow 0.3s;
}

.card:hover {
  transform: translateY(-5px);
  box-shadow: 0 10px 25px rgba(0, 0, 0, 0.2);
}

.card-header {
  background: #5c6bc0;
  color: white;
  padding: 15px 20px;
  font-size: 1.3rem;
  display: flex;
  align-items: center;
}

.card-header i {
  margin-right: 10px;
  font-size: 1.5rem;
}

.card-body {
  padding: 20px;
}

.btn-group {
  display: flex;
  flex-wrap: wrap;
  gap: 10px;
  margin-bottom: 15px;
}

.btn {
  flex: 1;
  min-width: 150px;
  background: #5c6bc0;
  color: white;
  border: none;
  padding: 12px 15px;
  border-radius: 8px;
  font-size: 1rem;
  cursor: pointer;
  transition: all 0.3s;
  display: flex;
  align-items: center;
  justify-content: center;
  box-shadow: 0 3px 6px rgba(0,0,0,0.16);
}

.btn i {
  margin-right: 8px;
}

.btn:hover {
  background: #3f51b5;
  transform: translateY(-2px);
  box-shadow: 0 5px 10px rgba(0,0,0,0.2);
}

.btn:active {
  transform: translateY(1px);
}

.btn-delete {
  background: #e53935;
}

.btn-delete:hover {
  background: #c62828;
}

.btn-success {
  background: #43a047;
}

.btn-success:hover {
  background: #2e7d32;
}

.result-area {
  background: #f5f5f5;
  border-radius: 8px;
  padding: 15px;
  margin-top: 20px;
  min-height: 150px;
  max-height: 300px;
  overflow-y: auto;
  font-family: 'Courier New', monospace;
  font-size: 0.95rem;
  border: 1px solid #e0e0e0;
}

.log-entry {
  padding: 8px 0;
  border-bottom: 1px dashed #e0e0e0;
}

.log-entry:last-child {
  border-bottom: none;
}

.success {
  color: #2e7d32;
}

.error {
  color: #c62828;
}

.info {
  color: #1565c0;
}

.data-table {
  width: 100%;
  border-collapse: collapse;
  margin-top: 15px;
}

.data-table th {
  background: #e8eaf6;
  text-align: left;
  padding: 10px 15px;
  font-weight: 600;
}

.data-table td {
  padding: 10px 15px;
  border-bottom: 1px solid #e0e0e0;
}

.data-table tr:nth-child(even) {
  background-color: #f9f9f9;
}

.status-bar {
  background: #e3f2fd;
  padding: 10px 15px;
  border-radius: 8px;
  margin-top: 15px;
  font-size: 0.9rem;
}

footer {
  text-align: center;
  color: rgba(255, 255, 255, 0.7);
  margin-top: 30px;
  padding: 20px;
  font-size: 0.9rem;
}

@media (max-width: 600px) {
  .container {
    padding: 10px;
  }
  
  .btn-group {
    flex-direction: column;
  }
  
  h1 {
    font-size: 1.8rem;
  }
}
</style>